USE MASTER
GO
IF EXISTS (SELECT * FROM sys.databases WHERE [name]='RestaurantBooking') DROP DATABASE [RestaurantBooking]
GO
CREATE DATABASE [RestaurantBooking]
GO
USE [RestaurantBooking]
GO
CREATE TABLE [Dishes]
(
	[ID] INT IDENTITY(1,1) PRIMARY KEY,
	[Name] NVARCHAR(50) NOT NULL UNIQUE,
	[Image] VARCHAR(100),
	[Price] INT NOT NULL,
)

CREATE TABLE [Sample_Meals]
(
	[ID] INT IDENTITY(1,1) PRIMARY KEY,
	[Name] NVARCHAR(50) NOT NULL UNIQUE,
	[Menu] VARCHAR(50) NOT NULL,
)

CREATE TABLE [Users]
(
	[ID] INT IDENTITY(1,1) PRIMARY KEY,
	[Username] VARCHAR(30) NOT NULL UNIQUE,
	[Password] VARCHAR(30) NOT NULL,
	[IsAdmin] BIT DEFAULT 0,
	[Name] VARCHAR(30),
	[Company] VARCHAR(50),
	[Email] VARCHAR(30),
	[Phone] VARCHAR(15),
	[Activation] BIT DEFAULT 0,
)

CREATE TABLE [Orders]
(
	[ID] INT IDENTITY(1,1) PRIMARY KEY,
	[CustomerID] INT REFERENCES [Users](ID) NOT NULL,
	[Menu] VARCHAR(50) NOT NULL,
	[Quantity] SMALLINT NOT NULL,
	[Price] INT NOT NULL,
	[Order_Time] DATETIME NOT NULL,
	[Note] VARCHAR(1000),
	[Status] VARCHAR(20) CHECK ([Status] in ('Approved','Waiting','Refused')) DEFAULT 'Waiting',
)
GO

INSERT INTO [Dishes] VALUES('Dish 1','dish1.jpg',10000)
INSERT INTO [Dishes] VALUES('Dish 2','dish2.jpg',20000)
INSERT INTO [Dishes] VALUES('Dish 3','dish3.jpg',30000)
INSERT INTO [Dishes] VALUES('Dish 4','dish4.jpg',40000)
INSERT INTO [Dishes] VALUES('Dish 5','dish5.jpg',50000)
INSERT INTO [Dishes] VALUES('Dish 6','dish6.jpg',60000)
INSERT INTO [Dishes] VALUES('Dish 7','dish7.jpg',70000)
INSERT INTO [Dishes] VALUES('Dish 8','dish8.jpg',80000)

GO
INSERT INTO [Sample_Meals] VALUES('Sample Meal 1','1,2,4,6,7')
INSERT INTO [Sample_Meals] VALUES('Sample Meal 2','2,3,4,7,8')
INSERT INTO [Sample_Meals] VALUES('Sample Meal 3','1,5,6,7,8')

GO
INSERT INTO [Users] VALUES('Admin','123456',1,NULL,NULL,NULL,NULL,1)
INSERT INTO [Users] VALUES('Hungnl','123456',0,'Nguyen Luu Hung','P0812L','abc@yahoo.com','0984512120',1)
INSERT INTO [Users] VALUES('Thanhdt','123456',0,'Dao Trong Thanh','P0812L','xyz@yahoo.com','2353456457',1)
INSERT INTO [Users] VALUES('Vinhdl','123456',0,'Duong Le Vinh','Teachers','Vinhdl@yahoo.com','0934583244',1)

GO

USE MASTER
GO

IF EXISTS (SELECT * FROM syslogins WHERE NAME='admin')
	EXEC SP_DROPLOGIN 'admin'
GO

EXEC SP_ADDLOGIN 'admin', '123456', 'RestaurantBooking'

GO

USE [RestaurantBooking]

GO
EXEC SP_GRANTDBACCESS 'admin'
GO
GRANT ALL TO [admin]
GO
GRANT ALL ON [Dishes] to [admin]
GRANT ALL ON [Sample_Meals] to [admin]
GRANT ALL ON [Users] to [admin]
GRANT ALL ON [Orders] to [admin]

GO
SELECT * FROM [Dishes] compute SUM(Price)
SELECT * FROM [Sample_Meals] 
SELECT * FROM [Users] 
SELECT * FROM [Orders] 